from base.web import SHandler, get_files, BaseHandler, UserHandler, str_sql
import time


class create_order(UserHandler):
    # 将product格式化为value格式
    def create_values(self):
        pro = eval(self.get_argument('product', ''))
        values = []
        for p in pro:
            value = '''
                ((select last_insert_id() as id limit 0, 1), {0}, {1})
            '''.format(p.get('id'), p.get('num'))
            values.append(value)
        self.insert_product_params = ','.join(values)
    # 查看是否有正在下单的订单

    def is_has_current(self):
        try:
            return self.query_sql('select count(*) as total where userid = {0} and status = 4'.format(userid))[0]['total']
        except:
            return None
    # 新增订单

    def add_order(self):
        insert_order_sql = '''
            insert into hs_order(userid) values ({0})
        '''

        insert_product_sql = '''
            insert into hs_order_product(orderid, productid, num) values {0}
        '''
        self.userid = int(self.get_argument('userid', self.userid))
        self.create_values()
        print(insert_product_sql.format(self.insert_product_params))
        if self.insert_sql([insert_order_sql.format(self.userid), insert_product_sql.format(self.insert_product_params)]):
            return True
        else:
            return False

    def post(self):
        # 查看是否有购物车
        # if (not self.is_has_current()) and self.add_order():
        if self.add_order():
            res = self.res_success('insert')
        else:
            res = self.res_error('insert')
        self.write(res)


class query_order(UserHandler):
    def get_users(self):
        name = self.get_argument('name', '')
        if self.userid:
            self.id_params = self.userid
        else:
            try:
                user = self.query_sql(
                    '''select id from hs_user where (name like \'%{name}%\' or shortname like \'%{name}%\' or phone like \'%{name}%\')'''.format(name=name))
            except:
                user = None
            if user:
                id_list = []
                for u in user:
                    id_list.append(str(u['id']))
                self.id_params = ','.join(id_list)
            else:
                self.id_params = 0
    def post(self):
        ispay = int(self.get_argument('ispay', 0))
        status = int(self.get_argument('status', 0))
        current_page = int(self.get_argument('current_page', 1))
        page_size = int(self.get_argument('page_size', 10000))
        starttime = int(self.get_argument('starttime', 0) or 0)
        endtime = (int(self.get_argument('endtime', False) or (time.time()) + 86400))
        self.get_users()
        condition = dict(
            id_params=self.id_params,
            ispay=ispay,
            status=status,
            ispayQ='=' if ispay else '!=',
            statusQ='=' if status else '!=',
            start=(current_page - 1) * page_size,
            starttime=time.strftime('%Y-%m-%d', time.localtime(starttime)),
            endtime=time.strftime('%Y-%m-%d', time.localtime(endtime)),
            num=page_size
        )
        order_list = self.query_sql(
            'select * from hs_order where ctime between \'{starttime}\' and \'{endtime}\' and userid in ({id_params}) and ispay {ispayQ} {ispay} and status {statusQ} {status}  order by -ctime limit {start},{num}'.format(**condition))
        total = self.query_sql(
            'select count(*) as total from hs_order where ctime between \'{starttime}\' and \'{endtime}\' and userid in ({id_params}) and ispay {ispayQ} {ispay} and status {statusQ} {status}'.format(**condition))
        
        for order in order_list:
            order['ctime'] = str(order['ctime'])
            order['product'] = ','.join([i['name'] for i in self.query_sql('''
                select name from hs_product where id in (
                    select productid from hs_order_product where orderid = {0}
                )
            '''.format(order['id']))])
            try:
                _user = self.query_sql(
                    'select id, phone, address, name from hs_user where id = {0}'.format(order['userid']))
            except:
                _user = None
            if _user:
                order['user'] = _user[0]
        res = self.res_success('query', data=order_list,
                               object=dict(total=total[0]['total']))
        self.write(res)


class order_detail(UserHandler):
    def get_product(self):
        try:
            self._product = self.query_sql(
                'select * from hs_order_product where orderid = {0}'.format(self.order['id']))
            id_list = [str(i['productid']) for i in self._product]
            self.products = self.query_sql(
                'select * from hs_product where id in ({0})'.format(','.join(id_list)))
            for p in self.products:
                p['ctime'] = str(p['ctime'])
                for _ in self._product:
                    if p['id'] == _['productid']:
                        p['num'] = _['num']
                        p['o_pid'] = _['id']
                        break
            self.order['product'] = self.products
            return True
        except:
            return False
    def get_user(self):
        try:
            self.userid = self.order['userid']
            self.user = self.query_sql(
                'select * from hs_user where id = {0}'.format(self.userid))[0]
            self.order['user'] = self.user
            return True
        except:
            return False
    def get_orderid(self):
        orderid = self.get_argument('orderid', 0)
        if orderid:
            self.orderid = orderid
        else:
            self.orderid_arr = self.query_sql('select id from hs_order where userid = {0} and status < 4'.format(self.userid))
            self.orderid = self.orderid_arr[0]['id'] if len(self.orderid_arr) else 0
    def get_total(self):
        total_sql = '''
            SELECT SUM(a.num * b.price) as total FROM hs_order_product a, hs_product b where a.productid = b.id and a.orderid = {0}
        '''.format(self.orderid)
        self.total = self.query_sql(total_sql)[0]['total']
    def post(self):
        self.get_orderid()
        self.get_total()
        if self.orderid:
            
            self.order = self.query_sql(
                'select * from hs_order where id = {0}'.format(self.orderid))[0]
            self.order['ctime'] = str(self.order['ctime'])
            self.order['total'] = self.total
            self.get_user()
            if not self.get_product():
                self.order['product'] = []
        else:
            self.order = {}
        res = self.res_success('query', object=self.order)
            
        self.write(res)


class current_order(UserHandler):
    def post(self):
        userid = self.get_argument('userid', self.userid)
        try:
            order = self.query_sql(
                'select * from hs_order where userid = {0} and status = 1'.format(userid))[0]
        except:
            order = None
        if order:
            product_id_list = []
            for p in eval(order['product']):
                product_id_list.append(str(p[0]))
            product = self.query_sql(
                'select * from hs_product where id in ({0})'.format(','.join(product_id_list)))
            for pro in product:
                for p in eval(order['product']):
                    if pro['id'] == p[0]:
                        pro['num'] = p[1]
                pro['ctime'] = str(pro['ctime'])
            res = self.res_success('query', object=dict(product_list=product))
        else:
            res = self.res_error('query')
        self.write(res)


class add_product(UserHandler):
    def post(self):
        orderid = int(self.get_argument('orderid', 0))
        try:
            product = eval(self.get_argument('product', None))
        except:
            product = None
        condition = [
            orderid,
            product['id'],
            product['num']
        ]
        try:
            ishas = self.query_sql(
                'select id from hs_order_product where orderid = {0} and productid = {1}'.format(*condition))[0]
        except:
            ishas = dict(
                id=0
            )
        if ishas['id']:
            self.modify_sql(['update hs_order_product set num = {0} where id = {1}'.format(
                condition[2], ishas['id'])])
            res = self.res_success('modify')
        else:
            if self.insert_sql(['insert into hs_order_product(orderid, productid, num) values({0}, {1}, {2})'.format(*condition)]):
                res = self.res_success('modify')
            else:
                res = self.res_error('modify')
        self.write(res)


class del_product(UserHandler):
    def post(self):
        order_product_id = self.get_argument('order_product_id', 0)
        try:
            self.modify_sql(
                ['delete from hs_order_product where id = {0}'.format(order_product_id)])
            res = self.res_success('modify')
        except:
            res = self.res_error('modify')
        self.write(res)


class set_status(UserHandler):
    def post(self):
        status = int(self.get_argument('status', 0))
        orderid = int(self.get_argument('orderid'), 0)
        condition = [
            status,
            orderid
        ]
        if self.modify_sql(['update hs_order set status = {0} where id = {1}'.format(*condition)]):
            res = self.res_success('modify')
        else:
            res = self.res_error('modify')
        self.write(res)


class set_pay(UserHandler):
    def post(self):
        status = int(self.get_argument('status', 0))
        orderid = int(self.get_argument('orderid'), 0)
        condition = [
            status,
            orderid
        ]
        if self.modify_sql(['update hs_order set ispay = {0} where id = {1}'.format(*condition)]):
            res = self.res_success('modify')
        else:
            res = self.res_error('modify')
        self.write(res)


class modify_order(UserHandler):
    def post(self):
        orderid = int(self.get_argument('id', 0))
        filters = ['status', 'ispay']
        params = []
        for key in filters:
            value = int(self.get_argument(key, 0))
            if value:
                params.append(key + '=' + str(value))

        condition = [
            ','.join(params),
            orderid
        ]
        if self.modify_sql(['update hs_order set {0} where id = {1}'.format(*condition)]):
            res = self.res_success('modify')
        else:
            res = self.res_error('modify')
        self.write(res)


class insert_cart(UserHandler):
    def check_product_has(self):
        self.get_cartid()
        if self.ishas:
            get_productids_sql = '''
                SELECT productid, id from hs_cart_product where cartid = {0}
            '''
            get_ishas_id_sql = '''
                SELECT num from hs_cart_product where cartid = {0} and productid = {1}
            '''
            cart_product_id = int(self.get_argument('productid', 0))
            productids = self.query_sql(get_productids_sql.format(self.cartid))
            if len([i for i in productids if i['productid'] == cart_product_id]) > 0:
                self.has_num = self.query_sql(get_ishas_id_sql.format(self.cartid, cart_product_id))[0]['num']
            else:
                self.has_num = 0
        else:
            self.has_num = 0
    def get_cartid(self):
        self.userid = int(self.userid) or int(self.get_argument('userid'))
        try:
            self.cartid = self.query_sql('select id from hs_cart where userid = {0} and status = 0'.format(self.userid))[0]['id']
            self.ishas = True
        except:
            self.cartid = '(SELECT t.id from ( select id from hs_cart WHERE userid = {0} and status = 0) t)'.format(self.userid)
            self.ishas = False
    def make_params(self):
        self.check_product_has()
        create_list = ['productid', 'num']
        params = [
            str(self.cartid)
        ]
        for key in create_list:
            value = self.get_argument(key)
            params.append(str(value))
        self.condition = ','.join(params)

    def post(self):
        self.make_params()
        insert_cart_sql = '''
            INSERT INTO hs_cart ( userid )
            VALUES
                ( {0} )
        '''
        insert_cart_product_sql = '''
            INSERT INTO hs_cart_product ( cartid, productid, num )
            VALUES
                ({0})
        '''
        update_cart_product_sql = '''
            UPDATE hs_cart_product set num = {0} where cartid = {1} and productid = {2}
        '''.format(self.has_num + int(self.get_argument('num')), self.cartid, self.get_argument('productid'))
        update_hs_cart_sql = '''
            UPDATE hs_cart c 
            SET c.money = (
                SELECT
                    SUM( a.price * b.num ) 
                FROM
                    hs_product AS a,
                    hs_cart_product AS b 
                WHERE
                    b.productid = a.id
                    AND c.id = b.cartid
                ) WHERE c.id = {0}
        '''
        sql_arr = [
            insert_cart_sql.format(self.userid),
            insert_cart_product_sql.format(self.condition) if not self.has_num else update_cart_product_sql,
            update_hs_cart_sql.format(self.cartid)
        ]
        if self.ishas:
            sql_arr = sql_arr[1:]
        if self.insert_sql(sql_arr):
            res = self.res_success('insert')
        else:
            res = self.res_error('insert')
        self.write(res)

class cancel_cart_product(UserHandler):
    def get_cart_productid(self):
        self.cart_productid = self.get_argument('id')

    def get_cartid(self):
        self.get_cart_productid()
        self.cartid = self.query_sql('select cartid from hs_cart_product where id = {0}'.format(self.cart_productid))[0]['cartid']

    def get_carts_length(self):
        self.get_cartid()
        self.length = self.query_sql('select COUNT(*) as total from hs_cart_product where cartid = {0}'.format(self.cartid))[0]['total']
    def post(self):
        self.get_carts_length()
        delete_product_sql = '''
            delete from hs_cart_product where id = {0}
        '''.format(self.cart_productid)
        update_money_sql = '''
            UPDATE 
                hs_cart 
            SET money = (
                SELECT
                    SUM( hcp.num * hp.price ) 
                FROM
                    hs_product hp,
                    hs_cart_product hcp 
                WHERE
                    hp.id = hcp.productid 
                    AND 
                    hcp.cartid = {0}
            ) WHERE id = {0}
        '''.format(self.cartid)

        update_money_0_sql = '''
            UPDATE 
                hs_cart 
            SET money = 0 WHERE id = {0}
        '''.format(self.cartid)
        if self.length == 1:
            sqls = [delete_product_sql, update_money_0_sql]
        else:
            sqls = [delete_product_sql, update_money_sql]
        if self.modify_sql(sqls):
            res = self.res_success('delete')
        else:
            res = self.res_error('delete')
        self.write(res)
class modify_cart_product(UserHandler):
    def post(self):
        id = self.get_argument('id')
        num = self.get_argument('num')
        add_sql = '''
            update hs_cart_product hcp set hcp.num = {0} where id = {1}
        '''
        money_sql = '''
            UPDATE 
                hs_cart 
            SET money = (
                SELECT
                    SUM( hcp.num * hp.price ) 
                FROM
                    hs_product hp,
                    hs_cart_product hcp 
                WHERE
                    hp.id = hcp.productid 
                    AND 
                    hcp.cartid = ( SELECT cartid FROM hs_cart_product WHERE id = {0} )
            ) WHERE id = ( SELECT cartid FROM hs_cart_product WHERE id = {0} ) 
        '''
        if self.modify_sql([add_sql.format(num, id), money_sql.format(id)]):
            res = self.res_success('modify')
        else:
            res = self.res_error('modify')
        self.write(res)

class query_cart(UserHandler):
    def post(self):
        userid = self.userid or self.get_argument('userid')
        try:
            data = self.query_sql('''
                select product.name, product.price, cartProduct.id, cartProduct.productid, cartProduct.num from hs_product as product ,
                hs_cart_product as cartProduct,
                hs_cart as cart where cart.userid = {0} and cart.status = 0 and 
                cart.id = cartProduct.cartid and cartProduct.productid = product.id
            '''.format(userid))
            total = self.query_sql('select money from hs_cart where userid = {0} and status = 0'.format(self.userid))[0]['money']
        except:
            data = []
            total = 0
        res = self.res_success('query', data = data, object = dict(total = total))
        self.write(res)

class cart_to_order(UserHandler):
    def check_order(self):
        self.userid = self.userid or self.get_argument('userid')
        check_order_sql = '''
            select id from hs_order where userid = {0} and status < 4
        '''.format(self.userid)
        orderid_arr = self.query_sql(check_order_sql)
        self.has = orderid_arr[0]['id'] if len(orderid_arr) else 0
    def post(self):
        self.check_order()
        if self.has:
            res = self.res_error('insert', code = 2)
        else:
            get_product_sql = '''
                SELECT
                    productid,
                    num 
                FROM
                    hs_cart_product 
                WHERE
                    cartid = ( SELECT id FROM hs_cart WHERE userid = {0} AND status = 0 )
            '''
            get_last_orderid = '(SELECT LAST_INSERT_ID() from hs_order limit 0,1)'
            insert_order_product = '''
                INSERT INTO hs_order_product (orderid, productid, num) VALUES {0}
            '''
            insert_order_sql = '''
                INSERT INTO hs_order (userid) VALUES ({0})
            '''
            update_cart_sql = '''
                UPDATE hs_cart set status = 1 where userid = {0}
            '''
            product_list = self.query_sql(get_product_sql.format(self.userid))
            params = ','.join([
                '({0}, {1}, {2})'.format(get_last_orderid, str(p['productid']), str(p['num']))
                for p in product_list
            ])
            sq_list = [
                insert_order_sql.format(self.userid),  #往订单表插入数据
                insert_order_product.format(params), #往订单货物表插入数据
                update_cart_sql.format(self.userid) #将购物车得状态置为已经变成订单
            ]
            if self.insert_sql(sq_list):
                res = self.res_success('insert')
            else:
                res = self.res_error('insert')
        self.write(res)
        

order_url = [
    (r'/api/order/create_order', create_order),
    (r'/api/order/current_order', current_order),
    (r'/api/order/add_product', add_product),
    (r'/api/order/query_order', query_order),
    (r'/api/order/order_detail', order_detail),
    (r'/api/order/modify_order', modify_order),
    (r'/api/order/del_product', del_product),
    (r'/api/order/set_status', set_status),
    (r'/api/order/set_pay', set_pay),
    (r'/api/order/insert_cart', insert_cart),
    (r'/api/order/cancel_cart_product', cancel_cart_product),
    (r'/api/order/modify_cart_product', modify_cart_product),
    (r'/api/order/query_cart', query_cart),
    (r'/api/order/cart_to_order', cart_to_order),
]


        
